import sqlite3
import os
from getCRC import *
from pathutil import *
import logging
import ConfigParser


logging.basicConfig(level=logging.DEBUG, filename='xbfm.log',
                    format='%(asctime)s %(levelname)s: %(message)s',
                    datefmt='%Y-%m-%d %H:%M:%S')

Config = ConfigParser.ConfigParser()
Config.read("config.ini")
SQLDIR = Config.get('SETTINGS','sqldir')
DBUSER = Config.get('SETTINGS','dbuser')
DBPASS = Config.get('SETTINGS','dbpass')
DBTYPE = Config.get('SETTINGS','dbtype')
DBNAME = Config.get('SETTINGS','dbname')
DBIP   = Config.get('SETTINGS','dbip')
PORT = Config.get('SETTINGS','port')# For Python MySQL
CHARSET = 'utf8'
UNICODE = True
WARNINGS = True
CONNTYPE ='Sqlite' #Default db connector

MyDB = ''
if DBTYPE == 'Sqlite3':
   
    if os.path.isfile(SQLDIR):
        MyDB = SQLDIR
        CONNTYPE = 'Sqlite3'
        strConnect=sqlite3.connect(MyDB)

if DBTYPE == 'MySql':

    try:
        import MySQLdb
        CONNTYPE = 'MySQLdb'
        MyDB = "MySQL Native"
#   python mysql client
    except:
        try:
            import mysql.connector as MySQLpy
            CONNTYPE = 'MySQLpy'
            MyDB = 'MySQL Python'
            connstring = {
                'host': DBIP,
                'port': PORT,
                'database': DBNAME,
                'user': DBUSER,
                'password': DBPASS,
                'charset': CHARSET,
                'use_unicode':UNICODE,
                'get_warnings':WARNINGS,
                }
        except:
            wx.MessageBox("Could not load any SQL Client!")
            Return
#if DBTYPE =='MySql':
#
#    MyDB = DBIP +':'+DBNAME
#    dbType = DBTYPE
#    strConnect = 'MySQLdb.connect(%s'%DBIP+',%s'%DBUSER +',%s'%DBPASS +',%s'%DBNAME+')'


logging.debug(u"SQL DB = %s" %MyDB)

def getMaxID():
    if CONNTYPE == 'Sqlite3':
        conn = sqlite3.connect(MyDB)
    elif CONNTYPE == 'MySQLdb':
        conn = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
    elif CONNTYPE == 'MySQLpy':    
        conn = MySQLpy.connect(**connstring)
    cursor = conn.cursor()
    #print "Lookup up %s" % OldPath
    sql = "select max(idPath) from path"
    cursor.execute(sql)
    maxID = cursor.fetchone()
    #print sql + " returned %s" %PathID
    conn.close
    return maxID


#Get all the current directories listed as paths and use for starting
#folder view.  Also used to check if Destination folder already exists.

def getStartFolder():
    if CONNTYPE == 'Sqlite3':
        conn = sqlite3.connect(MyDB)
    elif CONNTYPE == 'MySQLdb':
        conn = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
    elif CONNTYPE == 'MySQLpy':    
        conn = MySQLpy.connect(**connstring)
    cursor = conn.cursor()
    sql = "Select strPath FROM path where strContent = 'tvshows' and strPath NOT LIKE('%multipath%')"
    cursor.execute(sql)
    StartFolder = cursor.fetchone()
    logging.debug(u"Source Folder = %s" % StartFolder)
    conn.close
    return StartFolder

def getPath(idPath):

    if CONNTYPE == 'Sqlite3':
        conn = sqlite3.connect(MyDB)
    elif CONNTYPE == 'MySQLdb':
        conn = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
    elif CONNTYPE == 'MySQLpy':    
        conn = MySQLpy.connect(**connstring)
        #  conn = sqlite3.connect(MyDB)
    cursor = conn.cursor()
    #print "Lookup up %s" % OldPath
    sql = "select strPath from path where idPath = '%s'" % idPath
    cursor.execute(sql)
    strPath = cursor.fetchone()
    
    conn.close
    return strPath

# When moving files we need to find the records to update the path
def getPathID(OldPath):

    if CONNTYPE == 'Sqlite3':
        conn = sqlite3.connect(MyDB)
    elif CONNTYPE == 'MySQLdb':
        conn = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
    elif CONNTYPE == 'MySQLpy':    
        conn = MySQLpy.connect(**connstring)
        #  conn = sqlite3.connect(MyDB)
    cursor = conn.cursor()
    #print "Lookup up %s" % OldPath
    sql = "select idPath from path where strPath = '%s'" % OldPath
    cursor.execute(sql)
    PathID = cursor.fetchone()
    #print sql + " returned %s" %PathID
    conn.close
    return PathID
 #Check number of records before and after moving to see if they match

def testFilePaths(idPathNew):
    if CONNTYPE == 'Sqlite3':
        conn = sqlite3.connect(MyDB)
    elif CONNTYPE == 'MySQLdb':
        conn = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
    elif CONNTYPE == 'MySQLpy':    
        conn = MySQLpy.connect(**connstring)
    sql = "Select count from file where %s"%idPath+"= %s" %isPathNew
    cursor.execute(sql)
    recCount = cursor.fetchone()
    conn.close
    return recCount

def setFilePath(idPathOld,idPathNew,strFile):
    if CONNTYPE == 'Sqlite3':
        conn = sqlite3.connect(MyDB)
    elif CONNTYPE == 'MySQLdb':
        conn = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
    elif CONNTYPE == 'MySQLpy':    
        conn = MySQLpy.connect(**connstring)
    cursor = conn.cursor()
    sql = "update files set idPath = %s" %idPathNew + " where idPath = %s" % idPathOld + " and strFilename ='%s'" %strFile
    #print sql
    logging.debug(u"SQL = %s" %sql)
    try:
        cursor.execute(sql)
    except:
        return 0
    conn.commit()
    if DBTYPE == 'Sqlite3':
        nbr_changed = conn.total_changes
    if DBTYPE == 'MySql':
        nbr_changed = cursor.rowcount
    conn.close
    # Change the thumb for the file
    oldcrcname = getPath(idPathOld)
    oldcrcname = oldcrcname[0]+strFile
    newcrcname = getPath(idPathNew)
    newcrcname = newcrcname[0]+strFile
    newcrc = getCrc(newcrcname)
    if len(newcrc) == 7:
        newcrc ="0%s"%newcrc
    oldcrc = getCrc(oldcrcname)
    if len(oldcrc) == 7:
        oldcrc = "0%s"%oldcrc
    if oldcrc !='' and newcrc !='':
        setHash(oldcrc,newcrc,oldcrcname)
    logging.debug(u"%s" %nbr_changed +" Path Record changed for %s" %strFile)
    print "%s" %nbr_changed +" Path Record changed for %s" %strFile
    return nbr_changed

# Try to find first content type searching from the bottom to the top of the path
def getContentTypeOrg(strPath):

    if CONNTYPE == 'Sqlite3':
        conn = sqlite3.connect(MyDB)
    elif CONNTYPE == 'MySQLdb':
        conn = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
    elif CONNTYPE == 'MySQLpy':    
        conn = MySQLpy.connect(**connstring)
    cursor = conn.cursor()
    #print "Look up content type for: %s" % strPath

    path = strPath
    mountpt = getMount(strPath)

    while path != getMount(strPath):
        path = os.path.split(path)[0]
        testpath = path + os.sep
        sql = "select strContent from path where strPath = '%s'" % testpath
        #logging.debug(u"SQL = %s" %sql)
        cursor.execute(sql)
        strContent = cursor.fetchall()
        for row in strContent:
            Content = row[0]
            #print testpath + " returned %s" %Content
            if Content != "None":
                continue
        if Content != "None":
            break

    conn.close
    #print testpath + " returned Final %s" %Content
    return testpath,Content

def getContentType(strPath):
 #Searches the old path records to find the video source record
    if CONNTYPE == 'Sqlite3':
        conn = sqlite3.connect(MyDB)
    elif CONNTYPE == 'MySQLdb':
        conn = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
    elif CONNTYPE == 'MySQLpy':    
        conn = MySQLpy.connect(**connstring)
    conn.text_factory = str
    
    cursor = conn.cursor()
    #print "Look up content type for: %s" % strPath

    path = strPath
    mountpt = getMount(strPath)
    Content=''
    while path != getMount(strPath):
        path = os.path.split(path)[0]
        testpath = path + os.sep
        sql = "select strContent from path where strPath = '%s'" % testpath
        logging.debug(u"SQL = %s" %sql)
        cursor.execute(sql)

        try: strContent = cursor.fetchone()
        except:
            break
        if cursor.rowcount > 0:
            for row in strContent:
                tstContent = row
                if tstContent == "tvshows":
                    Content = tstContent
                    return Content
                if tstContent == "movies":
                    Content = tstContent
                return Content
    conn.close
    #print testpath + " returned Final %s" %Content
    return testpath,Content



def addPath(strPath,strContent,strScraper):
    if CONNTYPE == 'Sqlite3':
        conn = sqlite3.connect(MyDB)
    elif CONNTYPE == 'MySQLdb':
        conn = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
    elif CONNTYPE == 'MySQLpy':    
        conn = MySQLpy.connect(**connstring)
    cursor = conn.cursor()
    maxID = getMaxID()
    maxidPath = int(maxID[0])
    newidPath = maxidPath +1
    sql = "insert INTO path (idPath, strPath, strContent, strScraper) values (%s"% newidPath+",'%s'" %strPath + ",'%s'"%strContent + ",'%s'"%strScraper+")"
    #print sql
    logging.debug(u"SQL = %s" %sql)
    cursor.execute(sql)
    conn.commit()
    conn.close


#Given a parent path find all the child paths and any video records tied
#to those paths then change the idPath in files to new idPath
def setAllPaths(SourcePath,DestPath):


    totalRecs=0
    maxID = getMaxID()
    maxidPath = int(maxID[0])
# Bug fix 3/21/2011- Need to change query - could return extra records such as
#"Doctor Who (2005) when run from "Doctor Who" - added os.sep on end
# of source path. Should return only records under parent source.
# select all path records under destination then insert new path records
#  At least one record of the old path should have a content type.
    if CONNTYPE == 'Sqlite3':
        conn = sqlite3.connect(MyDB)
    elif CONNTYPE == 'MySQLdb':
        conn = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
    elif CONNTYPE == 'MySQLpy':    
        conn = MySQLpy.connect(**connstring)
    cursor = conn.cursor()
    srcPath = SourcePath
    if not srcPath.endswith(os.sep):
        srcPath = srcPath + os.sep
    sql = "Select idPath, strPath, strContent from path where strPath like '%s" %srcPath + "%'"
    #print sql
    cursor.execute(sql)
    SourcePathRecords = cursor.fetchall()

    conn.close
        
    for row in SourcePathRecords:
                       

        if SourcePath.endswith(os.sep):
            SourcePath = SourcePath[0:-1]
        if DestPath.endswith(os.sep):
            DestPath = DestPath[0:-1]
        srcBase,srcTail = os.path.split(SourcePath)
        destBase,destTail = os.path.split(DestPath)
#from /media/WDDrive/TVSeries we wind up with
#  /media/WDDrive and TVSeries from Source  - No trailing os.sep
#  /media/hitachi and TVSeries from Dest
#  Now we need to remove everything left of TVSeries from SourcePath

#   /media/WDDrive/TVSeries/Lost/Season 04/
#   -/media/WDDrive = TVSeries/Lost/Season 04
        SourceID = row[0]
        RecordPath = row[1]
        
        MediaPath = RecordPath.replace(srcBase,'',1)
#Now join new base path with the new source
        if srcTail == destTail:
            NewMediaPath = destBase + MediaPath
        else:
            NewMediaPath = DestPath + MediaPath
#Path now should be
#  /media/Hitachi/TVSeries/Lost/Season 04/
# XBMC path records need trailing seperator
# Enter the new path into the path table
        strPath = NewMediaPath
        if CONNTYPE == 'Sqlite3':
            conn = sqlite3.connect(MyDB)
        elif CONNTYPE == 'MySQLdb':
            conn = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
        elif CONNTYPE == 'MySQLpy':    
            conn = MySQLpy.connect(**connstring)
        
        nbr_changed = 0       
        cursor = conn.cursor()
        # check to see if path is a TV Show
        
        sql = "select idShow from tvshowlinkpath where idPath = %s" %SourceID
        cursor.execute(sql)
        isShow = cursor.fetchone()
        if isShow > 0:
            
            sql =  "select c12 from episode where idEpisode in(select idEpisode from tvshowlinkepisode where tvshowlinkepisode.idShow = (select idShow from tvshowlinkpath  where tvshowlinkpath.idPath = (select idPath from path where path.strPath = '%s'" %RecordPath+ "))) group by c12"
            print sql            
            cursor.execute(sql)
            seasons = cursor.fetchall()
            tothashes = 0
            rowindex = 0            
            for row in seasons:
          #above returns a list of season numbers for series so take
          #results and process the series thumbs
                season = row[0]          
                newcrcname = "season"+strPath+"season %s" %season 
                oldcrcname = "season"+RecordPath+"season %s" %season 
                newcrc = getCrc(newcrcname)
                if len(newcrc) == 7:
                    newcrc ="0%s"%newcrc
                oldcrc = getCrc(oldcrcname)
                if len(oldcrc) == 7:
                    oldcrc = "0%s"%oldcrc
                if oldcrc !='' and newcrc !='':
                    hashes = setHash(oldcrc,newcrc,oldcrcname)
                tothashes = tothashes + hashes   
                rowindex = rowindex+1
            logging.debug("Season Thumbs Changed: %s" %tothashes +" of %s" %rowindex)
            print "Season Thumbs Changed: %s" %tothashes +" of %s" %rowindex
        #If a path record exists update it    
        sql = 'Update path set strPath = "%s"' %strPath +', strHash = NULL where idPath = %s' %SourceID
        try: 
            cursor.execute(sql)
            if DBTYPE == 'Sqlite3':
                nbr_changed = conn.total_changes
            if DBTYPE == 'MySql':
                nbr_changed = cursor.rowcount
        except:
            conn.close
            nbr_changed = 0
            logging.debug(u"Error Changing Path Record for %s" %MediaPath)
            #return nbr_changed
        conn.commit()
        totalRecs = totalRecs+1
        # Change TV Show thumb
        newcrcname = strPath
        oldcrcname = RecordPath
        newcrc = getCrc(newcrcname)
        if len(newcrc) == 7:
            newcrc ="0%s"%newcrc
        oldcrc = getCrc(oldcrcname)
        if len(oldcrc) == 7:
            oldcrc = "0%s"%oldcrc
        if oldcrc !='' and newcrc !='':
            setHash(oldcrc,newcrc,oldcrcname)
               
                
        logging.debug(u"%s" %nbr_changed +" Path Record changed for %s" %MediaPath)
        print "%s" %nbr_changed +" Path Record changed for %s" %MediaPath        
        conn.close

# addPath(NewPath, NewContent, NewScraper)
# We need to get the new path ID and set file records -This could be
# done in one step but for debug purposes it's split.
        #NewPathID = getPathID(strPath)
    # Now that the path is changed in the files table the metadata
    # hash needs to be recalculated and thumb names changed in
    # the cache folder. We get the crc value of the full path
    # of the video file.  Since we're in a loop this is the only
    # place we have all the file names and paths.
        if nbr_changed > 0:
            if CONNTYPE == 'Sqlite3':
                conn = sqlite3.connect(MyDB)
            elif CONNTYPE == 'MySQLdb':
                conn = MySQLdb.connect(DBIP,DBUSER,DBPASS,DBNAME)
            elif CONNTYPE == 'MySQLpy':    
                conn = MySQLpy.connect(**connstring)
            cursor = conn.cursor()
            sql = "Select strFilename from files where idPath = %s"%SourceID
            cursor.execute(sql)
            try: getFilenames = cursor.fetchall()
            except:
                break
            tothashes = 0
            rowindex = 0
            for row in getFilenames:
                 
                Filename = row[0]
                newcrcname = strPath+Filename
                oldcrcname = RecordPath+Filename
                newcrc = getCrc(newcrcname)
                if len(newcrc) == 7:
                    newcrc ="0%s"%newcrc
                oldcrc = getCrc(oldcrcname)
                if len(oldcrc) == 7:
                    oldcrc = "0%s"%oldcrc
                if oldcrc !='' and newcrc !='':
                    hashes  = setHash(oldcrc,newcrc,oldcrcname)
                    tothashes = tothashes + hashes
                rowindex = rowindex + 1   
            conn.close
            logging.debug("Episodes Thumbs Changed: %s" %tothashes +" of %s"%rowindex)
            print "Episodes Thumbs Changed: %s" %tothashes +" of %s"%rowindex
    #End of for Loop  Next record will be path minus Season 04
    #and repeat until the base path is reached.
    #logging.debug(u"Total Path ID changed %s" %totalRecs)
    return totalRecs
